﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace JZElectrick {
    public class SQLiteUtils {

        public static string DBPath = null;
        static SQLiteConnection _connection = null;

        public static string createUserTableSql = "create table if not exists test (" +
                                                  "uid integer not null unique primary key autoincrement, " +
                                                  "uname varchar(10) not null unique," +
                                                  "psw varchar(35)," +
                                                  "dorm integer" +
                                                  ")";

        public static string createDormTableSql = "create table if not exists dorm (" +
                                                  "dorm_id integer not null unique primary key autoincrement, " +
                                                  "full_name varchar(20) not null," +
                                                  "build_name varchar(20) not null," +
                                                  "floor_name varchar(10) not null," +
                                                  "room_name varchar(10) not null," +
                                                  "build_id vachar(8) not null," +
                                                  "floor_id varchar(8) not null," +
                                                  "room_id varchar(8) not null" +
                                                  ")";

        public static string createBalanceSql = "create table if not exists balance (" +
                                                "bid integer not null unique primary key autoincrement, " +
                                                "value varchar(8) not null," +
                                                "time varchar(12) not null," +
                                                "dorm integer not null" +
                                                ")";

        public static string insertUserSql = "insert into user (uname) values ('{0}')";
        public static string setPasswordSql = "update user set psw='{1}' where uname='{0}'";
        public static string getUserListSql = "select uname,psw,full_name,build_name,floor_name,room_name,build_id,floor_id,room_id " +
                                              "from(test left join dorm on test.dorm= dorm.dorm_id)";
        public static string getUserSql = "select uname,psw from user,dorm where uid='{0}' and dorm is and dorm_id=dorm";
        public static string getDormSql = "select full_name,build_name,floor_name,room_name,build_id,floor_id,room_id from dorm where dorm_id='{0}'";

        public static string addDormSql = "insert into dorm (full_name,build_name,floor_name,room_name,build_id,floor_id,room_id) " +
                                          "values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',)";

        private SQLiteUtils() {
            DBPath = Application.StartupPath + "\\data.db";
            
        }

        public static void InitDatabase() {
            if (!File.Exists(DBPath)) {
                SQLiteConnection.CreateFile(Application.StartupPath + "\\data.db");
            }
            _connection = new SQLiteConnection("Data Source=" + DBPath + ";version=3");
            _connection.Open();
            
            SQLiteCommand command = new SQLiteCommand(createUserTableSql, _connection);
            command.ExecuteNonQuery();
            
            command = new SQLiteCommand(createDormTableSql, _connection);
            command.ExecuteNonQuery();

            
            command = new SQLiteCommand(createBalanceSql, _connection);
            command.ExecuteNonQuery();

        }

        public static void AddUser(string username) {
            string insertIntoUserSql = string.Format(insertUserSql, username);
            SQLiteCommand command = new SQLiteCommand(insertIntoUserSql, _connection);
            command.ExecuteNonQuery();
        }

        public static void SetPassword(string username, string password) {
            string updatePswSql = string.Format(setPasswordSql, username, password);
            SQLiteCommand command = new SQLiteCommand(updatePswSql, _connection);
            command.ExecuteNonQuery();
        }

        public static List<User> GetUserList() {
            List<User> list = new List<User>();
            SQLiteCommand command = new SQLiteCommand(getUserSql, _connection);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read()) {
                User user = new User(reader["uname"].ToString());

                if (!string.IsNullOrWhiteSpace(reader["psw"].ToString())) {
                    user.Password = reader["psw"].ToString();
                }

                bool dormValidated = false;
                for (int i = 2; i < 9; i++) {
                    if (string.IsNullOrWhiteSpace(reader[i].ToString())) {
                        dormValidated = true;
                        break;
                    }
                }
                if (dormValidated) {
                    user.Dorm = new Dorm(reader[2].ToString(), reader[3].ToString(), reader[4].ToString(),
                                         reader[5].ToString(), reader[6].ToString(), reader[7].ToString(), reader[8].ToString());
                }

                list.Add(user);
            }
            return list;
        }
    }
}
